select
case when  monthsample.sampletime = '1' and  monthsample.legal!=0 then monthsample.legal else 0 end as Janlegal,
case when  monthsample.sampletime = '1' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Janillegal,
case when  monthsample.sampletime = '1' and  monthsample.wait!=0 then monthsample.wait else 0 end as Janwait,

case when  monthsample.sampletime = '2' and  monthsample.legal!=0 then monthsample.legal else 0 end as Feblegal,
case when  monthsample.sampletime = '2' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Febillegal,
case when  monthsample.sampletime = '2' and  monthsample.wait!=0 then monthsample.wait else 0 end as Febwait,

case when  monthsample.sampletime = '3' and  monthsample.legal!=0 then monthsample.legal else 0 end as Marlegal,
case when  monthsample.sampletime = '3' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Marillegal,
case when  monthsample.sampletime = '3' and  monthsample.wait!=0 then monthsample.wait else 0 end as Marwait,

case when  monthsample.sampletime = '4' and  monthsample.legal!=0 then monthsample.legal else 0 end as Aprlegal,
case when  monthsample.sampletime = '4' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Aprillegal,
case when  monthsample.sampletime = '4' and  monthsample.wait!=0 then monthsample.wait else 0 end as Aprwait,

case when  monthsample.sampletime = '5' and  monthsample.legal!=0 then monthsample.legal else 0 end as Maylegal,
case when  monthsample. sampletime = '5' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Mayillegal,
case when  monthsample.sampletime = '5' and  monthsample.wait!=0 then monthsample.wait else 0 end as Maywait,

case when  monthsample.sampletime = '6' and  monthsample.legal!=0 then monthsample.legal else 0 end as Junelegal,
case when  monthsample.sampletime = '6' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Juneillegal,
case when  monthsample.sampletime = '6' and  monthsample.wait!=0  then monthsample.wait else 0 end as Junewait,

case when  monthsample.sampletime = '7' and  monthsample.legal!=0 then monthsample.legal else 0 end as Julylegal,
case when  monthsample.sampletime = '7' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Julyillegal,
case when  monthsample.sampletime = '7' and  monthsample.wait!=0 then monthsample.wait else 0 end as Julywait,

case when  monthsample.sampletime = '8' and  monthsample.legal!=0 then monthsample.legal else 0 end as Auglegal,
case when  monthsample.sampletime = '8' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Augillegal,
case when  monthsample.sampletime = '8' and  monthsample.wait!=0 then monthsample.wait else 0 end as Augwait,

case when  monthsample.sampletime = '9' and  monthsample.legal!=0 then monthsample.legal else 0 end as Septlegal,
case when  monthsample.sampletime = '9' and  monthsample.illegal!=0 then monthsample.illegal else 0 end as Septillegal,
case when  monthsample.sampletime = '9' and  monthsample.wait!=0 then monthsample.wait else 0 end as Septwait,

case when  monthsample.sampletime = '10' and monthsample.legal!=0 then monthsample.legal  else 0 end as Octlegal,
case when  monthsample.sampletime = '10' and monthsample.illegal!=0 then monthsample.illegal  else 0 end as Octillegal,
case when  monthsample.sampletime = '10' and monthsample.wait!=0 then monthsample.wait  else 0 end as Octwait,

case when  monthsample.sampletime = '11' and monthsample.legal!=0 then monthsample.legal  else 0 end as Novlegal,
case when  monthsample.sampletime = '11' and monthsample.illegal!=0 then monthsample.illegal  else 0 end as Novillegal,
case when  monthsample.sampletime = '11' and monthsample.wait!=0 then monthsample.wait  else 0 end as Novwait,

case when  monthsample.sampletime = '12' and monthsample.wait!=0 then monthsample.legal  else 0 end as Decelegal,
case when  monthsample.sampletime = '12' and monthsample.illegal!=0 then monthsample.illegal else 0 end as Deceillegal,
case when  monthsample.sampletime = '12' and monthsample.wait!=0 then monthsample.wait  else 0 end as Decewait
from (
select
sam.sampletime,
SUM(legal) AS legal,
SUM(illegal) AS illegal,
SUM(wait) AS wait
from
(
select sample.id,
month(sample.create_time) as sampletime,
DATE_FORMAT(sample.create_time,'%Y-%m-%d') as timeall,
CASE WHEN report.appraCon='合法非法出版物'  THEN 1 ELSE 0 END AS legal,
CASE WHEN report.appraCon='合法非法出版物'  THEN 1 ELSE 0 END AS illegal,
CASE WHEN report.appraCon  is null   THEN 1 ELSE 0 END AS wait
from
id_identsample sample
left join
(
select id,appra_Con as appraCon from id_report
) report  on report.id=sample.by1
where 1=1 and DATE_FORMAT(sample.create_time,'%Y-%m')<=

concat(year(now()),'-12')

and DATE_FORMAT(sample.create_time,'%Y-%m')>=


concat(year(now()),'-01')


) sam group by sam.sampletime
) monthsample